package com.lecture.java8.excel.dyr.n.a002;

import com.alibaba.excel.EasyExcel;

import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

public class ExcelMerger {

    public static void main(String[] args) {
        // 原始Excel文件路径
        final String excel1FilePath = "D:\\study\\tmp\\1114\\可跑的数据.xlsx";
        final String excel2FilePath = "D:\\study\\tmp\\1114\\最终结果-分析师关注.xlsx";
        final String excel3FilePath = "D:\\study\\tmp\\1114\\上市公司绿色创新（匹配版）00-23年.xlsx";
        // 新的Excel文件路径
        final String outputFilePath = "D:\\study\\tmp\\1114\\1114-2筛选结果.xlsx";

        // 读取EXCEL1数据
        List<Excel1Data> excel1DataList = EasyExcel.read(excel1FilePath).head(Excel1Data.class).sheet().doReadSync();

        // 读取EXCEL2数据
        List<Excel2Data> excel2DataList = EasyExcel.read(excel2FilePath).head(Excel2Data.class).sheet().doReadSync();
        Map<String, Map<Integer, String>> excel2Map = excel2DataList.stream()
                .collect(Collectors.toMap(
                        data -> data.getStkcd() + "_" + data.getYear(),
                        data -> Collections.singletonMap(data.getYear(), data.getAnalyst2()),
                        (oldValue, newValue) -> oldValue
                ));

        // 读取EXCEL3数据
        List<Excel3Data> excel3DataList = EasyExcel.read(excel3FilePath).head(Excel3Data.class).sheet().doReadSync();
        Map<String, Map<Integer, Excel3Data>> excel3Map = excel3DataList.stream()
                .collect(Collectors.toMap(
                        data -> data.getStockCode() + "_" + data.getYear(),
                        data -> Collections.singletonMap(data.getYear(), data),
                        (oldValue, newValue) -> oldValue
                ));

        // 关联数据
        List<Excel1Data> mergedDataList = excel1DataList.stream().map(excel1Data -> {
            // 从EXCEL2中获取ANALYST2
            String key2 = excel1Data.getCode() + "_" + excel1Data.getYear();
            if (excel2Map.containsKey(key2)) {
                excel1Data.setAnalyst2(excel2Map.get(key2).get(excel1Data.getYear()));
            }

            // 从EXCEL3中获取绿色创新列
            String key3 = excel1Data.getCode() + "_" + excel1Data.getYear();
            if (excel3Map.containsKey(key3)) {
                Excel3Data excel3Data = excel3Map.get(key3).get(excel1Data.getYear());
                if (excel3Data != null) {
                    excel1Data.setGreenInnovation1(excel3Data.getGreenInnovation1());
                    excel1Data.setGreenInnovation2(excel3Data.getGreenInnovation2());
                    excel1Data.setGreenInnovation3(excel3Data.getGreenInnovation3());
                    excel1Data.setGreenInnovation4(excel3Data.getGreenInnovation4());
                    excel1Data.setGreenInnovation5(excel3Data.getGreenInnovation5());
                    excel1Data.setGreenInnovation6(excel3Data.getGreenInnovation6());
                }
            }
            return excel1Data;
        }).collect(Collectors.toList());

        // 将合并后的数据写入新的Excel文件
        EasyExcel.write(outputFilePath,Excel1Data.class).sheet("Sheet1").doWrite(mergedDataList);
    }
}
